package com.eportal.service.impl;

import com.eportal.aop.LogAnnotation;
import com.eportal.api.MdOpenAddAgentJson;
import com.eportal.api.MdOpenAddCommunityJson;
import com.eportal.api.MdOpenApi;
import com.eportal.base.BaseDAO;
import com.eportal.dao.TAgentEntityMapper;
import com.eportal.domain.City;
import com.eportal.domain.Sysopt;
import com.eportal.domain.TAgent;
import com.eportal.domain.TCommunity;
import com.eportal.exception.MdKnownRunTimeException;
import com.eportal.vo.controller.background.tagent.ReportVO;
import com.eportal.service.SysOptService;
import com.eportal.service.SysRolesTemplateService;
import com.eportal.service.TAgentService;
import com.eportal.util.*;
import com.eportal.vo.controller.background.tagent.TAgentSelectListVO;
import com.framework.jqgrid.JqGridPager;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.struts2.ServletActionContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpSession;
import java.io.File;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * 角色管理业务逻辑接口实现
 */
@Service("tAgentService")
public class TAgentServiceImpl implements TAgentService {
	private Map<String, Object> result = new HashMap<String, Object>();
	private static Logger log = Logger.getLogger(TAgentServiceImpl.class.getName());
	private static String PATH = "/upload/certificate/";
	@Autowired
	private SysRolesTemplateService sysRolesTemplateService;
	@Autowired
    private SysOptService sysOptService;
	@Resource(name="dao")
	private BaseDAO dao;

	@Resource
	private TAgentEntityMapper tAgentDao;


	public HttpSession getSession() {
        return ServletActionContext.getRequest().getSession();
    }
    @Override
    public List<TAgent> list(JqGridPager jqGridPager) {
		String sql = "select t.sid,t.areacode,t.agtcode,t.agtname,c.described,us.userNum,comun.comuNum,s.uname from t02_agtent t "
				+ "left join city c on t.areacode=c.dm "
				+ "left join sysopt s on t.sysoptid=s.sid "
				+ "left join (select comu.agtcode,count(sid) as comuNum from t03_community comu group by comu.agtcode) comun on t.agtcode=comun.agtcode "
				+ "left join (select u.agtcode,count(t.sid) as userNum from t04_users_community t left join t04_users u on t.userid= u.userid where t.status=1 group by u.agtcode) us on t.agtcode=us.agtcode  ";
    	return dao.query(sql, jqGridPager);
    }

    @Override
	public List<TAgent> list(String sql) {
    	return dao.query(sql);
	}

    @Override
    public TAgent add(TAgent tAgent) throws Exception {
        dao.save(tAgent);
        return tAgent;
    }

    @Override
    public void delete(Long id) {
    	TAgent role = (TAgent) dao.getById(TAgent.class, id);
        dao.delete(role);
    }

    @Override
    public void delete(List<Long> ids) {
        for (Long id : ids) {
            delete(id);
        }
    }

    public TAgent get(Long sid) {
        return (TAgent) dao.getById(TAgent.class, sid);
    }

    @Override
    public void update(TAgent tAgent) throws Exception {
        dao.update(tAgent);
    }
    public void update(Object entity) throws Exception {
    	try {
    		dao.update(entity);
        } catch (Exception e) {
            log.error(this,e);
        }

    }
    public Object save(Object obj) throws Exception {
    	dao.save(obj);
    	return obj;
    }

    public Object getObject(String hql) {
		return  dao.loadObject(hql);
	}

	@Override
	public TAgent get(String agtCode) {
		StringBuffer sb = new StringBuffer("from TAgent where agtCode='");
		sb.append(agtCode);
		sb.append("'");
		return (TAgent) dao.loadObject(sb.toString());
	}

	/**
     * 合作方新增保存
     */
    @LogAnnotation(operateDescribe="公司新增")
    public Map<String, Object> addMethod(TAgent tAgent,Sysopt user){
        String msg = ValidateUtil.validate(tAgent);
        if (msg == null || msg.trim().length() == 0) {
            try {

            	tAgent.setcUserName(Long.toString(user.getSid()));
            	tAgent.setcTime(new Timestamp(System.currentTimeMillis()));
            	tAgent.setStatus("0");
                add(tAgent);
                result.put("objectId", tAgent.getSid());
                result.put("code", 1);
                result.put("msg", "操作成功");
            } catch (Exception e) {
                log.error("合作方保存失败", e);
                result.put("code", 0);
                result.put("msg", "操作失败");
            }
        } else {
            result.put("code", 0);
            result.put("msg", msg);
        }
        return result;
    }

	/**
     * 合作方修改保存
     */
    @LogAnnotation(operateDescribe="公司修改")
    public Map<String, Object> updateMethod(TAgent tAgent){
        String msg = ValidateUtil.validate(tAgent);
        result.put("objectId", tAgent.getSid());
        if (msg == null || msg.trim().length() == 0) {
            try {
            	TAgent entity = get(tAgent.getSid());
            	entity.setAgtName(tAgent.getAgtName());
            	entity.setAgtContact(tAgent.getAgtContact());
            	entity.setAgtTel(tAgent.getAgtTel());
            	entity.setAreaCode(tAgent.getAreaCode());
            	entity.setAddress(tAgent.getAddress());
                update(entity);
                if (StringUtils.isNotBlank(tAgent.getComm())) {
                	Sysopt sysopt = sysOptService.get(Long.parseLong(entity.getAdminUser()));
                	sysopt.setPasswd(MD5.MD5Encode(tAgent.getComm()));
                	sysOptService.update(sysopt);
				}
                result.put("code", 1);
                result.put("msg", "操作成功");
            } catch (Exception e) {
                log.error("合作方更新失败", e);
                result.put("code", 0);
                result.put("msg", "操作失败");
            }
        } else {
            result.put("code", 0);
            result.put("msg", msg);
        }
        return result;
    }

	 /**
     * 合作方删除
     */
    @LogAnnotation(operateDescribe="公司删除")
    public Map<String, Object> deleteMethod(String param_ids){
        try {
        	result.put("objectId", param_ids);
            List<Long> ids = new LinkedList<Long>();
            for (String s : param_ids.split(",")) {
                ids.add(Long.parseLong(s));
            }
            delete(ids);
            result.put("code", 1);
            result.put("msg", "操作成功");
        } catch (Exception e) {
            log.error("合作方删除失败", e);
            result.put("code", 0);
            result.put("msg", "操作失败");
        }
        return result;
    }

    /**
     * 合作方注册新增
     */
	@Override
	@LogAnnotation(operateDescribe="公司注册提交")
	public Map<String, Object> tagentAddMethod(TAgent tagent, File file, String fileName) {

		long idNext  = dao.findSequence("seq_t02_agtent")+1000;
		try {
    		Map<String, Object> resultSysopt = sysOptService.creatAgtManager(tagent,idNext+"");
    		if (!"1".equals(resultSysopt.get("code").toString())) {
    			 result.put("code", 0);
    	         result.put("msg", resultSysopt.get("msg"));
    	         return result;
			}
    		if (file != null) {
    			boolean isImage = ValidateUtil.isImage(file);
	             if (!isImage) {
	            	 result.put("code", 0);
	            	 result.put("msg", "文件的格式不正确");
	                 return result;
	             }
	             result = UploadUtil.uplodeFile(file,fileName,PATH);
    			if (!"1".equals(result.get("code").toString())) {
    				return result;
    			}
    			tagent.setAgtAttr(result.get("agtAttr").toString());
			}
        	tagent.setSid(idNext);
        	tagent.setAgtCode(Long.toString(idNext));
        	tagent.setcTime(new Timestamp(System.currentTimeMillis()));
        	tagent.setReqTime(new Timestamp(System.currentTimeMillis()));
        	tagent.setStatus("0");
        	tagent.setAdminUser(resultSysopt.get("sid").toString());
        	tagent.setComm("");
        	add(tagent);

            result.put("code", 1);
            result.put("msg", "操作成功");
        } catch (Exception e) {
            log.error("合作方保存失败", e);
            result.put("code", 0);
            result.put("msg", "操作失败");
        }
    	return result;
	}

	@Override
	@LogAnnotation(operateDescribe="公司审核")
	public Map<String, Object> auditMethod(String sid, String status, String comm, String sysoptId,Sysopt user) throws Exception {
		result = new HashMap<String, Object>();
		result.put("objectId", sid);
		TAgent entity = get(Long.parseLong(sid));
		//更新tagent数据
     	entity.setaUserName(Long.toString(user.getSid()));
     	entity.setComm(comm);
     	entity.setaTime(new Timestamp(System.currentTimeMillis()));
     	entity.setStatus(status);
        if (!"1".equals(status)) {
			result.put("code", 1);
	        result.put("msg", "操作成功");
	        return result;
		}
        /*if(StringUtils.isBlank(sysoptId)){
        	result.put("code", 0);
	        result.put("msg", "未填写区域主管");
	        return result;
        }*/
        if(!StringUtils.isBlank(sysoptId)){
        	entity.setSysoptId(Long.parseLong(sysoptId));
        }
        update(entity);
        //同步数据到开放平台
        result = SyncTo(entity);
        if (!"1".equals(result.get("code").toString())) {
        	throw new MdKnownRunTimeException((String)result.get("msg"));
        }

		//主板厂商指定角色权限 todo Created by Saber on 2017/7/25 下午3:43
		if("2".equals(entity.getType())){// 0:普通合作方，1：锁厂合作方, 2:主板合作方
			//更新主板厂商角色
			sysOptService.updateManager(entity.getAdminUser(),"7");//指定 7 为主板厂商角色权限
		}else {
			//创建合作方超级管理员角色
			result = sysRolesTemplateService.creatManager(entity);
			if ("0".equals(result.get("code").toString())) {
				return result;
			}
			//更新超级管理员角色权限
			sysOptService.updateManager(entity.getAdminUser(),result.get("roleId").toString());
		}

		//如果是锁厂和主板厂商，要创建测试的社区，来安装硬件   //type  1:锁厂      2:主板厂商
		if ("1".equals(entity.getType()) || "2".equals(entity.getType()) ) { ////todo Updated by Saber on 2017/7/25 下午2:03
			TCommunity tCommunity = new TCommunity();
			long idNext  = dao.findSequence("SEQ_T03_COMMUNITY");
			tCommunity.setSid(idNext);
			tCommunity.setAgtCode(entity.getAgtCode());
			tCommunity.setAreaCode(entity.getAreaCode());
			tCommunity.setDepartTel(entity.getAgtTel());
			if("2".equals(entity.getType())){
				tCommunity.setInviteUrl("主板厂商测试自动增加社区，无注册码");
			}else {
				tCommunity.setInviteUrl("锁厂测试自动增加社区，无注册码");
			}
			StringBuffer sb = new StringBuffer("from City where dm='");
			sb.append(entity.getAreaCode());
			sb.append("'");
			City city = (City) getObject(sb.toString());
			tCommunity.setCityCode(city.getDescribed());
			tCommunity.setCommunityName(entity.getAgtName()+"测试");
			tCommunity.setDepartName(tCommunity.getCommunityName());
			tCommunity.setType("1");
			result = SyncTo(tCommunity);
			if (!"1".equals(result.get("code").toString())) {
		        throw new MdKnownRunTimeException((String)result.get("msg"));
		    }else{
		    	tCommunity.setPlatDepartId(result.get("departid").toString());
		    	save(tCommunity);
		    }
		}

		result.put("code", 1);
	    result.put("msg", "操作成功");
		return result;
	}


	/**
	 * @Description: 同步合作方信息到开放平台
	 * @param entity
	 * @return
	 */
	public  Map<String, Object> SyncTo (TAgent entity){
		Map<String, Object> result = new HashMap<String, Object>();
		try {
//			Map<String,String> map = getPlatAppKey();
			Map<String,String> map = new HashMap<String, String>();
			map.put("agt_num", Config.MD_OPEN_AGT_NUM);
			map.put("app_key", Config.MD_OPEN_APP_KEY);
			MdOpenAddAgentJson json = MdOpenApi.addAgent(entity,map.get("agt_num"),map.get("app_key"));
    		if(json.isSuccess()){
    			entity.setPlatAgtcode(json.getData().getAgtcode());
    			entity.setPlatAppKey(json.getData().getAppkey());
    			update(entity);
    			result.put("code", 1);
      	        result.put("msg", "操作成功");
    		}else{
    			log.error("SyncTo fail,"+json.getMsg_());
				result.put("code", 2);
      	        result.put("msg", "公司审核成功，同步失败:"+json.getMsg().toString().replaceAll("[^0-9\\u4e00-\\u9fa5]", ""));
			}
    	} catch (Exception e) {
    		log.error(this, e);
    		result.put("code", 0);
  	        result.put("msg", "同步公司信息到开放平台失败");
    	}
		return result;
	}
	/**
	 * @Description: 同步社区信息到开放平台
	 * @param entity
	 * @return
	 */
	public Map<String, Object> SyncTo ( TCommunity tCommunity){
		Map<String, Object> result = new HashMap<String, Object>();
		try {
			Map<String,String> map = getPlatAppKey(tCommunity.getAgtCode());
			MdOpenAddCommunityJson json = MdOpenApi.addCommunity(tCommunity,map.get("agt_num"),map.get("app_key"));
			if(json.isSuccess()){
				result.put("departid", json.getData().getDepartid());
				result.put("code", 1);
      	        result.put("msg", "操作成功");
			}else{
				log.error("同步社区信息到开放平台失败，json:"+json);
				result.put("code", 1);
      	        result.put("msg", "社区审核成功，同步失败:"+json.getMsg().toString().replaceAll("[^0-9\\u4e00-\\u9fa5]", ""));
			}
    	} catch (Exception e) {
    		log.error("同步社区信息到开放平台失败", e);
    		result.put("code", 0);
  	        result.put("msg", "同步社区信息到开放平台失败");
    	}
		return result;
	}
	/**
	 * 合作方详情
	 */
	@Override
	public List<Map<String, String>> getDeatil(String where) {
		 String sql = "select t.sid,t.agtName,t.agtcode,t.plat_app_key,t.status,t.comm,t.ctime,t.areacode,t.agttel,TO_CHAR (t.reqtime,'YYYY-MM-DD hh24:mi:ss') reqtime,t.agtcontact,t.address ,s.username as adminUser,n.username as auserName,c.described as described,t.app_type "
		 		+ "from "
		 		+ "t02_agtent t "
		 		+ "left join sysopt s on ( t.adminuser = s.sid) "
		 		+ "left join sysopt n on ( t.ausername = n.sid) "
		 		+ "left join city c on ( t.areacode = c.dm) "
		 		+where;
		 return dao.queryBySql(sql);
	}
	/**
	 * 审核合作方列表
	 */
	@Override
	public List<TAgent> auditList(JqGridPager page) {
//		 String sql = "select t.sid,t.agtName,t.agtcode,t.plat_app_key,TO_CHAR (t.atime,'YYYY-MM-DD hh24:mi:ss') atime,t.areacode,t.agttel,TO_CHAR (t.reqtime,'YYYY-MM-DD hh24:mi:ss') reqtime,t.agtcontact,t.address,t.agtAttr,t.status,t.comm,s.username as adminUser,n.username as auserName,c.described as described "
//			 		+ "from "
//			 		+ "t02_agtent t "
//			 		+ "left join sysopt s on ( t.adminuser = s.sid) "
//			 		+ "left join sysopt n on ( t.ausername = n.sid) "
//			 		+ "left join city c on ( t.areacode = c.dm) ";
		String sql = "select * from "
				+ "(select t.sid,t.agtName,t.agtcode,t.plat_app_key,TO_CHAR (t.atime,'YYYY-MM-DD hh24:mi:ss') atime,t.areacode,t.agttel,TO_CHAR (t.reqtime,'YYYY-MM-DD hh24:mi:ss') reqtime,t.agtcontact,t.address,t.agtAttr,t.status,t.comm,s.username as adminUser,n.username as auserName,c.described as described from t02_agtent t left join sysopt s on ( t.adminuser = s.sid) left join sysopt n on ( t.ausername = n.sid) left join city c on ( t.areacode = c.dm)  "
				+ "where (t.status='0' or t.status='3') order by t.reqtime desc) "
				+ "union all "
				+ "select * from ("
				+ "select t.sid,t.agtName,t.agtcode,t.plat_app_key,TO_CHAR (t.atime,'YYYY-MM-DD hh24:mi:ss') atime,t.areacode,t.agttel,TO_CHAR (t.reqtime,'YYYY-MM-DD hh24:mi:ss') reqtime,t.agtcontact,t.address,t.agtAttr,t.status,t.comm,s.username as adminUser,n.username as auserName,c.described as described from t02_agtent t left join sysopt s on ( t.adminuser = s.sid) left join sysopt n on ( t.ausername = n.sid) left join city c on ( t.areacode = c.dm) "
				+ "where (t.status='1' or t.status='2') order by t.reqtime desc) ";
    	return dao.query(sql, page);
	}

    public Map<String,String> getPlatAppKey(String agtCode){
    	Map<String,String> map = new HashMap<String, String>();
		Sysopt  user;
		//todo 判断调用当前方法的是struts还是springmvc 	Created by Saber on 2017/8/15 下午12:57
		if(getSession()!=null){
			user= (Sysopt)getSession().getAttribute("user");
		}else {
			//SpringMVC获取HttpRequest对象 进而获取session 然后获取user
			user= (Sysopt) CommonUtil.getHttpRequest().getSession().getAttribute("user");//Created by Saber on 2017/8/15 下午12:58
		}
//    	Sysopt  user= (Sysopt)getSession().getAttribute("user");//todo 仅注释本行 Updated by Saber on 2017/8/15 下午12:57
//		if("-1".equals(user.getIsAdmin())){
//			map.put("agt_num", Config.MD_OPEN_AGT_NUM);
//			map.put("app_key", Config.MD_OPEN_APP_KEY);
//		 }else{
			 if (StringUtils.isBlank(agtCode)) {
				 agtCode = user.getAgtCode();
			 }
			 TAgent tagent = get(Long.parseLong(agtCode));
			 map.put("agt_num",tagent.getPlatAgtcode());
			 map.put("app_key", tagent.getPlatAppKey());
//		 }
		return map;
    }
    /**
     * 资料修改
     */
	@Override
	@LogAnnotation(operateDescribe="公司资料修改")
	public Map<String, Object> tagentDetailMethod(TAgent tAgent, File file, String fileName) {
		try {
			TAgent entity = get(tAgent.getSid());
    		if (file != null) {
    			boolean isImage = ValidateUtil.isImage(file);
	             if (!isImage) {
	            	 result.put("code", 0);
	            	 result.put("msg", "文件的格式不正确");
	                 return result;
	             }
	             result = UploadUtil.uplodeFile(file,fileName,PATH);
	   			if (!"1".equals(result.get("code").toString())) {
	   				return result;
	   			}
    			entity.setAgtAttr(result.get("agtAttr").toString());
			}
    		entity.setAgtName(tAgent.getAgtName());
    		entity.setAgtContact(tAgent.getAgtContact());
    		entity.setAgtTel(tAgent.getAgtTel());
    		entity.setAreaCode(tAgent.getAreaCode());
    		entity.setAddress(tAgent.getAddress());
    		entity.setuTime(new Timestamp(System.currentTimeMillis()));
    		if ("2".equals(entity.getStatus())) {
    			entity.setStatus("0");
			}
        	update(entity);
        	if (StringUtils.isNotBlank(tAgent.getComm())) {
        		Sysopt sysopt = sysOptService.get(Long.parseLong(entity.getAdminUser()));
             	sysopt.setPasswd(MD5.MD5Encode(tAgent.getComm()));
             	sysOptService.update(sysopt);
			}
            result.put("code", 1);
            result.put("msg", "操作成功");
        } catch (Exception e) {
            log.error("合作方保存失败", e);
            result.put("code", 0);
            result.put("msg", "操作失败");
        }
    	return result;
	}
	/**
	 * 平台数据统计
	 * @see TAgentServiceImpl#getPmsReports(Sysopt, int)  mybatis版
	 * @return
	 */
	@Override
	public List<Map<String, String>> getPmsReports(int userRole, Sysopt user) {
		StringBuffer sql = new StringBuffer();
		if (userRole == 1 || userRole == 2) {
			sql.append(esql("公司","ctime","t02_agtent","where t.status=1"));
			sql.append(" union all ");
			sql.append(esql("小区","ctime","t03_community","where t.status=1"));
			sql.append(" union all ");
			sql.append(esql("楼栋","ctime","t031_cbuilding",null));
			sql.append(" union all ");
			sql.append(esql("单元","ctime","t032_cinstallunit",null));
			sql.append(" union all ");
			sql.append(esql("房间","ctime","t035_room",null));
			sql.append(" union all ");
			sql.append(esql("住户","ctime","t04_users_community","where t.status=1"));
			sql.append(" union all ");
			sql.append(esql("报修","ctime","FIX_APPLY","where t.status<>'2' and t.user_del='1' and t.admin_del='1'"));
			sql.append(" union all ");
			sql.append(esql("接单","ctime","FIX_APPLY","where t.status='3' and t.user_del='1' and t.admin_del='1'"));
			sql.append(" union all ");
			sql.append(esql("完成","ctime","FIX_APPLY","where t.status='4' and t.user_del='1' and t.admin_del='1'"));
			sql.append(" union all ");
			sql.append(esql("缴费","ctime","FEE_DETAIL","where t.status='3'"));
			sql.append(" union all ");
			sql.append(esql("公告通知","ctime","T033_COMMUNITY_MSG",null));
			sql.append(" union all ");
			sql.append(esql("智能门禁","install_time","t05_locks",null));
			sql.append(" union all ");
			sql.append(esql("开门记录","otime","topenlog",null));
			sql.append(" union all ");
			sql.append(esql("好邻居动态","ctime","t04_user_album",null));
			sql.append(" union all ");
			sql.append(esql("回复","ctime","t04_user_albumcomment",null));
			sql.append(" union all ");
			sql.append(esql("点赞","ctime","t04_user_album_like",null));
			sql.append(" union all ");
			sql.append(esql("乐享动态","ctime","t04_users_enjoy",null));
//			sql.append(" union all ");
//			sql.append(esql("回复","ctime","t04_users_enjoy_comm",null));
		}else{
			String where = "where t.status=1 and t.sysoptId="+user.getSid();
			String where2 = "left join t02_agtent a on a.agtcode=t.agtcode where t.status=1 and a.sysoptId="+user.getSid();
			String where3 = "left join t02_agtent a on a.agtcode=t.agtcode where a.sysoptId="+user.getSid();
			String leftWhere = "left join t04_users u on t.userid= u.userid left join t02_agtent a on a.agtcode=u.agtcode where a.sysoptId="+user.getSid();
			String leftWhere2 = "left join t04_users u on t.userid= u.userid left join t02_agtent a on a.agtcode=u.agtcode where t.status=1 and a.sysoptId="+user.getSid();
			String leftWhere3 = "left join t03_community c on t.communityid= c.sid left join t02_agtent a on a.agtcode=c.agtcode where a.sysoptId="+user.getSid();
			String leftWhere4 = "left join t031_cbuilding cb on cb.sid = t.cbuildingid left join t03_community c on cb.communityid= c.sid left join t02_agtent a on a.agtcode=c.agtcode where a.sysoptId="+user.getSid();
			String leftWhere5 = "left join t032_cinstallunit cu on cu.sid = t.unit_id left join t031_cbuilding cb on cb.sid = cu.cbuildingid left join t03_community c on cb.communityid= c.sid left join t02_agtent a on a.agtcode=c.agtcode where a.sysoptId="+user.getSid();
			String leftWhere6 = "left join t04_users u on t.Apply_Userid= u.userid left join t02_agtent a on a.agtcode=u.agtcode where a.sysoptId="+user.getSid();

			sql.append(esql("合作方","ctime","t02_agtent",where));
			sql.append(" union all ");
			sql.append(esql("小区","ctime","t03_community",where2));
			sql.append(" union all ");
			sql.append(esql("楼栋","ctime","t031_cbuilding",leftWhere3));
			sql.append(" union all ");
			sql.append(esql("单元","ctime","t032_cinstallunit",leftWhere4));
			sql.append(" union all ");
			sql.append(esql("房间","ctime","t035_room",leftWhere5));
			sql.append(" union all ");
			sql.append(esql("住户","ctime","t04_users_community",leftWhere2));
			sql.append(" union all ");
			sql.append(esql("报修","ctime","FIX_APPLY",leftWhere6+" and t.status<>'2' and t.user_del='1' and t.admin_del='1'"));
			sql.append(" union all ");
			sql.append(esql("接单","ctime","FIX_APPLY",leftWhere6+" and t.status='3' and t.user_del='1' and t.admin_del='1'"));
			sql.append(" union all ");
			sql.append(esql("完成","ctime","FIX_APPLY",leftWhere6+" and t.status='4' and t.user_del='1' and t.admin_del='1'"));
			sql.append(" union all ");
			sql.append(esql("缴费","ctime","FEE_DETAIL",where3+ " and t.status='3'"));
			sql.append(" union all ");
			sql.append(esql("公告通知","ctime","T033_COMMUNITY_MSG",leftWhere3));
			sql.append(" union all ");
			sql.append(esql("智能门禁","install_time","t05_locks",where3));
			sql.append(" union all ");
			sql.append(esql("开门记录","otime","topenlog",where3));
			sql.append(" union all ");
			sql.append(esql("好邻居动态","ctime","t04_user_album",leftWhere));
			sql.append(" union all ");
			sql.append(esql("回复","ctime","t04_user_albumcomment",leftWhere));
			sql.append(" union all ");
			sql.append(esql("点赞","ctime","t04_user_album_like",leftWhere));
			sql.append(" union all ");
			sql.append(esql("乐享动态","ctime","t04_users_enjoy",leftWhere));
//			sql.append(" union all ");
//			sql.append(esql("回复","ctime","t04_users_enjoy_comm",null));

		}
		System.out.println(sql.toString());
	    return dao.queryBySql(sql.toString());
	}

	 public String esql(String cname,String ctime,String tableName,String where){
		StringBuffer sb = new StringBuffer("select '");
		sb.append(cname);
		sb.append("' as cname,");
		sb.append("count(t.sid) as countNum,");
		sb.append("nvl(sum(case when t.");
		sb.append(ctime);
		sb.append(" > TO_TIMESTAMP(sysdate) then 1 else 0 end),0) as a,");
		sb.append("nvl(sum(case when t.");
		sb.append(ctime);
		sb.append(" > TO_TIMESTAMP(sysdate - 1) and t.");
		sb.append(ctime);
		sb.append(" < TO_TIMESTAMP(sysdate) then 1 else 0 end),0) as b,");
		sb.append("nvl(sum(case when t.");
		sb.append(ctime);
		sb.append(" > TO_TIMESTAMP(sysdate - 2) and t.");
		sb.append(ctime);
		sb.append(" < TO_TIMESTAMP(sysdate - 1) then 1 else 0 end),0) as c,");
		sb.append("nvl(sum(case when  t.");
		sb.append(ctime);
		sb.append(" > TO_TIMESTAMP(sysdate - 3) and t.");
		sb.append(ctime);
		sb.append(" < TO_TIMESTAMP(sysdate - 2) then 1 else 0 end),0) as d,");
		sb.append("nvl(sum(case when t.");
		sb.append(ctime);
		sb.append(" > TO_TIMESTAMP(sysdate - 4) and t.");
		sb.append(ctime);
		sb.append(" < TO_TIMESTAMP(sysdate - 3) then 1 else 0 end),0) as e,");
		sb.append("nvl(sum(case when t.");
		sb.append(ctime);
		sb.append(" > TO_TIMESTAMP(sysdate - 5) and t.");
		sb.append(ctime);
		sb.append(" < TO_TIMESTAMP(sysdate - 4) then 1 else 0 end),0) as f,");
		sb.append("nvl(sum(case when t.");
		sb.append(ctime);
		sb.append(" > TO_TIMESTAMP(sysdate - 6) and t.");
		sb.append(ctime);
		sb.append(" < TO_TIMESTAMP(sysdate - 5) then 1 else 0 end),0) as g,");
		sb.append("nvl(sum(case when t.");
		sb.append(ctime);
		sb.append(" > TO_TIMESTAMP(sysdate - 6) and t.");
		sb.append(ctime);
		sb.append(" < TO_TIMESTAMP(sysdate) then 1 else 0 end),0) as z,");
		sb.append("nvl(sum(case when t.");
		sb.append(ctime);
		sb.append(" > TO_TIMESTAMP(sysdate - 29) then 1 else 0 end),0) as h,");
		sb.append("nvl(sum(case when t.");
		sb.append(ctime);
		sb.append(">TO_TIMESTAMP(sysdate - 89) then 1 else 0 end),0) as i ");
//		if(StringUtils.isNotBlank(whereAppend)){
//			sb.append(", ");
//			sb.append(whereAppend);
//		}
		sb.append(" from ");
		sb.append(tableName);
		sb.append(" t ");
		if(StringUtils.isNotBlank(where)){
			sb.append(where);
		}
		return sb.toString();
	}
	//临时处理
	 public String esql2(String cname){
		StringBuffer sb = new StringBuffer("select '");
		sb.append(cname);
		sb.append("' as cname,sum(0) as countNum,");
		sb.append("sum(0) as a,sum(0)as b,sum(0)as c,"
				+ "sum(0)as d,sum(0)as e,sum(0)as f,"
				+ "sum(0)as g,sum(0)as z,sum(0)as h,sum(0)as i from dual");
		return sb.toString();
	}
	/**
	 * 合作方数据统计
	 * @see TAgentServiceImpl#tagentReports2(String)
	 * @return
	 */
	@Override
	public List<Map<String, String>> tagentReports(String agtCode) {
		StringBuffer sql = new StringBuffer();
		String where = "where t.agtcode='"+agtCode+"'";
		String leftWhere = "left join t04_users u on t.userid= u.userid where u.agtcode='"+agtCode+"'";
		String leftWhere3 = "left join t03_community c on t.communityid= c.sid  where c.agtcode='"+agtCode+"'";
		String leftWhere4 = "left join t031_cbuilding cb on cb.sid = t.cbuildingid left join t03_community c on cb.communityid= c.sid where c.agtcode='"+agtCode+"'";
		String leftWhere5 = "left join t032_cinstallunit cu on cu.sid = t.unit_id left join t031_cbuilding cb on cb.sid = cu.cbuildingid left join t03_community c on cb.communityid= c.sid where c.agtcode='"+agtCode+"'";
		String leftWhere6 = "left join t04_users u on t.Apply_Userid= u.userid where u.agtcode='"+agtCode+"'";


//		sql.append(esql("合作方","ctime","t02_agtent","where t.status=1 and t.agtcode='"+agtCode+"'"));
//		sql.append(" union all ");
		sql.append(esql("小区","ctime","t03_community","where t.status=1 and t.agtcode='"+agtCode+"'"));
		sql.append(" union all ");
		sql.append(esql("楼栋","ctime","t031_cbuilding",leftWhere3));
		sql.append(" union all ");
		sql.append(esql("单元","ctime","t032_cinstallunit",leftWhere4));
		sql.append(" union all ");
		sql.append(esql("房间","ctime","t035_room",leftWhere5));
		sql.append(" union all ");
		sql.append(esql("住户","ctime","t04_users_community",leftWhere+" and t.status=1"));
		sql.append(" union all ");
		sql.append(esql("报修","ctime","FIX_APPLY",leftWhere6+" and t.status<>'2' and t.user_del='1' and t.admin_del='1'"));
		sql.append(" union all ");
		sql.append(esql("接单","ctime","FIX_APPLY",leftWhere6+" and t.status='3' and t.user_del='1' and t.admin_del='1'"));
		sql.append(" union all ");
		sql.append(esql("完成","ctime","FIX_APPLY",leftWhere6+" and t.status='4' and t.user_del='1' and t.admin_del='1'"));
		sql.append(" union all ");
		sql.append(esql("缴费","ctime","FEE_DETAIL","where t.agtcode ='"+agtCode+"' and t.status='3'"));
		sql.append(" union all ");
		sql.append(esql("公告通知","ctime","T033_COMMUNITY_MSG",leftWhere3));
		sql.append(" union all ");
		sql.append(esql("智能门禁","install_time","t05_locks",where));
		sql.append(" union all ");
		sql.append(esql("开门记录","otime","topenlog",where));
		sql.append(" union all ");
		sql.append(esql("好邻居动态","ctime","t04_user_album",leftWhere));
		sql.append(" union all ");
		sql.append(esql("回复","ctime","t04_user_albumcomment",leftWhere));
		sql.append(" union all ");
		sql.append(esql("点赞","ctime","t04_user_album_like",leftWhere));
		sql.append(" union all ");
		sql.append(esql("乐享动态","ctime","t04_users_enjoy",leftWhere));
//		sql.append(" union all ");
//		sql.append(esql("回复","ctime","t04_users_enjoy_comm",null));
	    return dao.queryBySql(sql.toString());
	}

	/**
	 * 社区数据统计
	 * @param agtCode
	 * @param tCommunityId
	 * @see TAgentServiceImpl#tcomuReports2(String, String)  mybatis版
	 * @return
	 */
	@Override
	public List<Map<String, String>> tcomuReports(String agtCode, String tCommunityId) {
		StringBuffer sql = new StringBuffer();
		String where = "where t.communityid='"+tCommunityId+"'";
		String leftWhere = "left join t04_user_album t1 on t.albumid= t1.sid where t1.communityid='"+tCommunityId+"'";
		String leftWhere4 = "left join t031_cbuilding cb on cb.sid = t.cbuildingid  where cb.communityid='"+tCommunityId+"'";
		String leftWhere5 = "left join t032_cinstallunit cu on cu.sid = t.unit_id left join t031_cbuilding cb on cb.sid = cu.cbuildingid where cb.communityid='"+tCommunityId+"'";

		sql.append(esql("楼栋","ctime","t031_cbuilding","where t.communityid='"+tCommunityId+"'"));
		sql.append(" union all ");
		sql.append(esql("单元","ctime","t032_cinstallunit",leftWhere4));
		sql.append(" union all ");
		sql.append(esql("房间","ctime","t035_room",leftWhere5));
		sql.append(" union all ");
		sql.append(esql("住户","ctime","t04_users_community",where+" and t.status=1"));
		sql.append(" union all ");
		sql.append(esql("报修","ctime","FIX_APPLY",where+" and t.status<>'2' and t.user_del='1' and t.admin_del='1'"));
		sql.append(" union all ");
		sql.append(esql("接单","ctime","FIX_APPLY",where+" and t.status='3' and t.user_del='1' and t.admin_del='1'"));
		sql.append(" union all ");
		sql.append(esql("完成","ctime","FIX_APPLY",where+" and t.status='4' and t.user_del='1' and t.admin_del='1'"));
		sql.append(" union all ");
		sql.append(esql("缴费","ctime","FEE_DETAIL",where+" and t.status='3'"));
		sql.append(" union all ");
		sql.append(esql("公告通知","ctime","T033_COMMUNITY_MSG",where));
		sql.append(" union all ");
		sql.append(esql("智能门禁","install_time","t05_locks",where));
		sql.append(" union all ");
		sql.append(esql("开门记录","otime","topenlog",where));
		sql.append(" union all ");
		sql.append(esql("好邻居动态","ctime","t04_user_album",where));
		sql.append(" union all ");
		sql.append(esql("回复","ctime","t04_user_albumcomment",leftWhere));
		sql.append(" union all ");
		sql.append(esql("点赞","ctime","t04_user_album_like",leftWhere));
		sql.append(" union all ");
		sql.append(esql("乐享动态","ctime","t04_users_enjoy",where));
//		sql.append(" union all ");
//		sql.append(esql("回复","ctime","t04_users_enjoy_comm",null));
	    return dao.queryBySql(sql.toString());
	}
	@Override
	@LogAnnotation(operateDescribe="公司区域主管修改")
	public Map<String, Object> updateRegionManagerMethod(TAgent tAgent){
        result.put("objectId", tAgent.getSid());
        try {
        	TAgent entity = get(tAgent.getSid());
        	if (tAgent.getSysoptId() != 0) {
        		entity.setSysoptId(tAgent.getSysoptId());
        		update(entity);
			}
            result.put("code", 1);
            result.put("msg", "操作成功");
        } catch (Exception e) {
            log.error("公司区域主管更新失败", e);
            result.put("code", 0);
            result.put("msg", "操作失败");
        }
        return result;
    }



//todo -----------------------------------------------------------------------------------------


    /**
     * 合作方列表（下拉）
     *
     * @param user     当前用户
     * @param userRole 当前用户userRole
     * @return
     * @author Saber
     * @Date 2017/8/2 下午1:01
     */
    @Override
    public List<TAgentSelectListVO> selectList(Sysopt user, int userRole) {

        Long sysoptId = null;
        String agtCode = null;
        //userRole 1:平台超级管理员  2:平台普通管理员  3:合作方超级管理员
        if (userRole != 1 && userRole != 2 && userRole != 7 ) {  // 增加主板厂商 todo Updated by Saber on 2017/7/25 下午5:21
            if (userRole == 21) {
//                where.append(" and sysoptId =");
//                where.append(user.getSid());
                sysoptId = user.getSid();
            }else{
//                where.append(" and agtCode ='");
//                where.append(user.getAgtCode());
//                where.append("'");
                agtCode = user.getAgtCode();
            }
//            where.append(" and status = '1' ");
        }

        return tAgentDao.selectList(sysoptId,agtCode,userRole);
    }

	/**
	 * 平台数据统计
	 *
	 * @param user     当前用户
	 * @param userRole 当前用户userRole
	 * @return
	 * @author Saber
	 * @Date 2017/8/22 上午10:04
	 * @see TAgentServiceImpl#getPmsReports(int, Sysopt)
	 */
	@Override
	public List<ReportVO> getPmsReports(Sysopt user, int userRole) {

		return tAgentDao.getPmsReports(user,userRole);
	}

	/**
	 * 合作方数据统计
	 *
	 * @param agtCode 合作方code
	 * @return
	 * @author Saber
	 * @Date 2017/8/22 上午11:04
	 * @see TAgentService#tagentReports(String)
	 */
	@Override
	public List<ReportVO> tagentReports2(String agtCode) {

		return tAgentDao.getTagentReports(agtCode);
	}

	/**
	 * 社区统计数据
	 * @param agtCode     合作方code
	 * @param communityId 小区id
	 * @return
	 * @author Saber
	 * @Date 2017/8/22 上午11:48
	 * @see TAgentService#tcomuReports(String, String)
	 */
	@Override
	public List<ReportVO> tcomuReports2(String agtCode, String communityId) {

		return tAgentDao.getComunReport(agtCode,communityId);
	}
}
